package com.pdg.mcplugin.common.baseclasses;

import java.sql.ResultSet;
import java.sql.Statement;

import com.pdg.mcplugin.common.interfaces.TableProvider;
import com.pdg.mcplugin.common.interfaces.TableProviderKey;
import com.pdg.mcplugin.common.interfaces.TableProviderValue;

public class TableProviderBase<E extends PluginBase, D extends DataProviderBase<E>,K extends TableProviderKey,V extends TableProviderValue<V>>
	extends PluginClientBase<E>
	implements TableProvider<D,K,V> {

	private static final String ERROR_COULD_NOT_CREATE_TABLE_FORMAT = "Could not create table `%s`!";
	private static final String QUERY_CREATE_TABLE_FORMAT = "CREATE TABLE IF NOT EXISTS `%s` (%s,%s);";
	private static final String QUERY_CHECK_FOR_TABLE_FORMAT = "SELECT `name` FROM `sqlite_master` WHERE `type`='table' AND `name`='%s';";
	private static final String ERROR_COULD_NOT_CREATE_ENTRY_FORMAT = "Could not create entry in table `%s`!";
	private static final String QUERY_CREATE_ENTRY_FORMAT = "INSERT INTO `%s` (%s,%s) VALUES (%s,%s);";
	private static final String ERROR_COULD_NOT_RETRIEVE_ENTRY_FORMAT = "Could not retrieve entry from table `%s`!";
	private static final String ERROR_COULD_NOT_DELETE_ENTRY_FORMAT = "Could not delete entry from table `%s`!";
	private static final String ERROR_COULD_NOT_UPDATE_ENTRY_FORMAT = "Could not update entry in table `%s`!";
	private static final String QUERY_DELETE_ENTRY_FORMAT = "DELETE FROM `%s` WHERE %s;";
	private static final String QUERY_RETRIEVE_ENTRY_FORMAT = "SELECT %s FROM `%s` WHERE %s;";
	private static final String QUERY_UPDATE_ENTRY_FORMAT = "UPDATE %s SET %s WHERE %s;";
	private D dataProvider;
	private String tableName;
	private V referenceValue;
	private K referenceKey;

	public TableProviderBase(D theDataProvider,String theTableName,K theReferenceKey, V theReferenceValue) {
		super(theDataProvider.getPlugin());
		setDataProvider(theDataProvider);
		setTableName(theTableName);
		setReferenceKey(theReferenceKey);
		setReferenceValue(theReferenceValue);
	}

	private void setReferenceValue(V theReferenceValue) {
		referenceValue = theReferenceValue;
	}

	private void setReferenceKey(K theReferenceKey) {
		referenceKey = theReferenceKey;
	}

	private void setTableName(String theTableName) {
		tableName = theTableName;
	}
	
	protected String getTableName(){
		return tableName;
	}

	private void setDataProvider(D theDataProvider) {
		dataProvider = theDataProvider;
	}

	@Override
	public D getDataProvider() {
		return dataProvider;
	}

	public boolean create(K theKey, V theValue){
		ensureTableExists();
		return createEntry(theKey,theValue);
	}
	private boolean createEntry(K theKey, V theValue) {
		if(entryExists(theKey)) return false;
		try{
			Statement statement = getDataProvider().getConnection().createStatement();
			String theQuery = getCreateEntryQuery(theKey,theValue);
			statement.execute(theQuery);
			return true;
		}catch(Exception e){
			getLogger().severe(getPlugin().formatMessage(getErrorForCreateEntryFailure()));
			e.printStackTrace();
		}
		return false;
	}

	private String getCreateEntryQuery(K theKey, V theValue) {
		return String.format(QUERY_CREATE_ENTRY_FORMAT,getTableName(),theKey.getInsertFields(),theValue.getInsertFields(),theKey.getInsertValues(),theValue.getInsertValues());
	}

	private String getErrorForCreateEntryFailure() {
		return String.format(ERROR_COULD_NOT_CREATE_ENTRY_FORMAT, DataProviderBase.sqlSafeString(getTableName()));
	}

	protected void ensureTableExists() {
		try{
			//create a statement to check for inventory table
			Statement statement = getDataProvider().getConnection().createStatement();
			ResultSet result = statement.executeQuery(getCheckForTableQuery());
			
			//if not found...
			if(!result.next()){
				//..execute statement to create table
				statement = getDataProvider().getConnection().createStatement();
				statement.execute(getCreateTableQuery());
			}
		}catch(Exception e){
			//an error has occurred
			getLogger().severe(getPlugin().formatMessage(getErrorForCreateTableFailure()));
			e.printStackTrace();
		}	
	}

	private String getErrorForCreateTableFailure() {
		return String.format(ERROR_COULD_NOT_CREATE_TABLE_FORMAT, DataProviderBase.sqlSafeString(getTableName()));
	}

	private String getCreateTableQuery() {
		return String.format(QUERY_CREATE_TABLE_FORMAT,DataProviderBase.sqlSafeString(getTableName()),getReferenceKey().getCreateFields(),getReferenceValue().getCreateFields());
	}

	private TableProviderValue<V> getReferenceValue() {
		return referenceValue;
	}

	private TableProviderKey getReferenceKey() {
		return referenceKey;
	}

	private String getCheckForTableQuery() {
		return String.format(QUERY_CHECK_FOR_TABLE_FORMAT,DataProviderBase.sqlSafeString(getTableName()));
	}

	public boolean exists(K theKey){
		ensureTableExists();
		return entryExists(theKey);
	}
	private boolean entryExists(K theKey) {
		return retrieveEntry(theKey)!=null;
	}

	public V retrieve(K theKey){
		ensureTableExists();
		return retrieveEntry(theKey);
	}
	private V retrieveEntry(K theKey) {
		try{
			//create a statement to check for inventory table
			Statement statement = getDataProvider().getConnection().createStatement();
			String theQuery = getRetrieveQuery(theKey);
			ResultSet result = statement.executeQuery(theQuery);
			if(result.next()){
				return getReferenceValue().createValueFromResultSet(result);
			}
		}catch(Exception e){
			//an error has occurred
			getLogger().severe(getPlugin().formatMessage(getErrorForRetrieveFailure()));
			e.printStackTrace();
		}
		return null;
	}

	private String getRetrieveQuery(K theKey) {
		return String.format(QUERY_RETRIEVE_ENTRY_FORMAT, getReferenceValue().getSelectList(), DataProviderBase.sqlSafeString(getTableName()), theKey.getWhereClause());
	}

	private String getErrorForRetrieveFailure() {
		return String.format(ERROR_COULD_NOT_RETRIEVE_ENTRY_FORMAT, DataProviderBase.sqlSafeString(getTableName()));
	}

	public boolean update(K theKey, V theValue){
		ensureTableExists();
		return updateEntry(theKey,theValue);
	}
	private boolean updateEntry(K theKey, V theValue) {
		if(!entryExists(theKey)) return false;
		try{
			Statement statement = getDataProvider().getConnection().createStatement();
			String theQuery = getUpdateEntryQuery(theKey,theValue);
			statement.execute(theQuery);
			return true;
		}catch(Exception e){
			getLogger().severe(getPlugin().formatMessage(getErrorForUpdateFailure()));
			e.printStackTrace();
		}
		return false;
	}

	private String getUpdateEntryQuery(K theKey, V theValue) {
		return String.format(QUERY_UPDATE_ENTRY_FORMAT, DataProviderBase.sqlSafeString(getTableName()),theValue.getUpdateList(),theKey.getWhereClause());
	}

	private String getErrorForUpdateFailure() {
		return String.format(ERROR_COULD_NOT_UPDATE_ENTRY_FORMAT, DataProviderBase.sqlSafeString(getTableName()));
	}

	public boolean delete(K theKey){
		ensureTableExists();
		return deleteEntry(theKey);
	}

	private boolean deleteEntry(K theKey) {
		if(!entryExists(theKey)) return false;
		try{
			Statement statement = getDataProvider().getConnection().createStatement();
			statement.execute(getDeleteQuery(theKey));
			return true;
		}catch(Exception e){
			getLogger().severe(getPlugin().formatMessage(getErrorForDeleteFailure()));
			e.printStackTrace();
		}
		return false;
	}

	private String getDeleteQuery(K theKey) {
		return String.format(QUERY_DELETE_ENTRY_FORMAT,DataProviderBase.sqlSafeString(getTableName()),theKey.getWhereClause());
	}

	private String getErrorForDeleteFailure() {
		return String.format(ERROR_COULD_NOT_DELETE_ENTRY_FORMAT, DataProviderBase.sqlSafeString(getTableName()));
	}

}
